﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Net;
using System.Net.Mail;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Security;
using System.Diagnostics;
using System.Collections;
 

/// <summary>
/// Summary description for LinkToDatabase
/// </summary>
public class LinkToDatabase
{
    public static string strConn = "Data Source=.; Initial Catalog=UBOX; Integrated Security=True";
    //public static string strConn = "Data Source=.; Initial Catalog=UBOX; Integrated Security=True";                        

	public LinkToDatabase()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    public static Boolean CheckInput(string s, string type)
    {
        Regex Regex1 = null;
        switch (type)
        {
            case "1":
                Regex1 = new Regex(@"^(?=.*\d)(?=.*[a-z])(?=.*[A-Z]).{8,20}$", RegexOptions.IgnoreCase);
                return Regex1.IsMatch(s);
            case "2":
                Regex1 = new Regex(@"^09[0-9]{2}[0-9]{6}$", RegexOptions.IgnoreCase);
                return Regex1.IsMatch(s);
            case "3":
                Regex1 = new Regex(@"^[A-Za-z\u4E00-\u9FA5\uF900-\uFA2D]+$", RegexOptions.IgnoreCase);
                return Regex1.IsMatch(s);
            case "4":
                Regex1 = new Regex(@"^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$", RegexOptions.IgnoreCase);
                return Regex1.IsMatch(s);
            default:
                return false;
        }

    }

    public User GetUserInfo(string userName)
    {
        UboxContext dc = new UboxContext();

        var uboxUser = dc.user
                                    .Where(u => u.user_username == userName)
                                    .Select(u => new User
                                    {
                                        ID = u.user_id,
                                        Password = u.user_password,
                                        Random = u.user_password_random,
                                        Name = u.user_username,
                                        Visible = (bool)u.user_visible,
                                        Picture = u.user_picture
                                    })
                                    .FirstOrDefault();

        if (uboxUser != null)
        {
            dc.Dispose();
            return uboxUser;
        }
        dc.Dispose();
        return null;
    }

    public User GetUserInfo(int id)
    {
        UboxContext dc = new UboxContext();

        var uboxUser = dc.user
                                    .Where(u => u.user_id == id)
                                    .Select(u => new
                                    {
                                        ID = u.user_id,
                                        Name = u.user_username,
                                        Visible = u.user_visible,
                                        Picture = u.user_picture
                                    })
                                    .FirstOrDefault();

        if (uboxUser != null)
        {
            User uInfo = new User();
            uInfo.ID = uboxUser.ID;
            uInfo.Name = uboxUser.Name;
            uInfo.Picture = uboxUser.Picture;
            uInfo.Visible = (bool)uboxUser.Visible;
            dc.Dispose();
            return uInfo;
        }
        dc.Dispose();
        return null;
    }

    #region 給user新密碼
    public bool GetUserPwd(string userName, string userEmail)
    {

        string RandPwd = RandomString();
        UboxContext db = new UboxContext();


        var uboxUser = db.user.FirstOrDefault(u => u.user_username == userName && u.user_email == userEmail);


        if (uboxUser != null)
        {
            Random i = new Random(Guid.NewGuid().GetHashCode());
            string salt = i.Next(0, 9999999).ToString();
            string buff = RandPwd + salt;
            string temp = FormsAuthentication.HashPasswordForStoringInConfigFile(buff, "sha1");
            string Name = uboxUser.user_username;
            uboxUser.user_password = temp;
            uboxUser.user_password_random = salt;
            db.SaveChanges();
            // Mail Message Setting
            string fromEmail = "uboxstudio@gmail.com";
            string fromName = "UBOX";
            MailAddress from = new MailAddress(fromEmail, fromName, Encoding.UTF8);
            string toEmail = userEmail;
            MailMessage mail = new MailMessage(from, new MailAddress(toEmail));
            string subject = "UBOX客服信件, 關於您的密碼提示...";
            mail.Subject = subject;
            mail.SubjectEncoding = Encoding.UTF8;
            string body = "<p><span style='font-size:14px'>親愛的"+Name+",</span></p>"+
                                    "<p>&nbsp;</p>"+
                                    "<p><span style='font-size:14px'>請您使用新密碼登入，登入後請記得更改您的密碼，以保障您帳戶的安全。</span></p>"+
                                    "<p><span style='font-size:14px'>您的新密碼為：<span style='color:rgb(0, 0, 255)'>" + RandPwd + "</span></span></p>" +
                                    "<p>&nbsp;</p>"+
                                    "<p>&nbsp;</p>"+
                                    "<p><span style='font-size:14px'>UBOX客服團隊 敬上</span></p>";

            mail.Body = body;
            mail.BodyEncoding = Encoding.UTF8;
            mail.IsBodyHtml = true;
            mail.Priority = MailPriority.High;
            // SMTP Setting
            SmtpClient client = new SmtpClient();
            client.Host = "smtp.gmail.com";
            client.Port = 587;
            client.Credentials = new NetworkCredential("uboxstudio@gmail.com", "iiicecm03");
            client.EnableSsl = true;
            // Send Mail
            client.Send(mail);


            db.Dispose();
            return true;
        }
        db.Dispose();
        return false;
    }
    #endregion

    #region 產生亂數密碼
    private static Random random = new Random((int)DateTime.Now.Ticks);

    private string RandomString()
    {
        StringBuilder builder = new StringBuilder();
        char ch;
        for (int i = 0; i < 12; i++)
        {
            ch = Convert.ToChar(Convert.ToInt32(Math.Floor(26 * random.NextDouble() + 65)));
            builder.Append(ch);
        }

        return builder.ToString();
    }
    #endregion

    private int _p1, _p2, _p3, _p4, _p5;
    public void SaveCompareRecord(int[] ProductsID)
    {
        UboxContext db = new UboxContext();
        int count = ProductsID.Length;
        switch (count)
        {
            case 2:
                _p1 = ProductsID[0];
                _p2 = ProductsID[1];
                SaveCP2Record();
                break;
            case 3:
                _p1 = ProductsID[0];
                _p2 = ProductsID[1];
                _p3 = ProductsID[2];
                SaveCP3Record();
                break;
            case 4:
                _p1 = ProductsID[0];
                _p2 = ProductsID[1];
                _p3 = ProductsID[2];
                _p4 = ProductsID[3];
                SaveCP4Record();
                break;
            case 5:
                _p1 = ProductsID[0];
                _p2 = ProductsID[1];
                _p3 = ProductsID[2];
                _p4 = ProductsID[3];
                _p5 = ProductsID[4];
                SaveCP5Record();
                break;
        }
    }
          
    private void SaveCP2Record()
    {
        using (SqlConnection conn = new SqlConnection(strConn))
        {
            string strCmd = "IF EXISTS (SELECT * FROM compare_2_products " +
                                         "WHERE product_1 = @p1 and product_2 = @p2 ) " +
                                         "UPDATE compare_2_products SET [count]=[count]+1 " +
                                         "WHERE product_1 = @p1 and product_2 = @p2 " +
                                         "ELSE " +
                                         "INSERT INTO compare_2_products (product_1,product_2,[count]) VALUES (@p1,@p2,1)";
            using (SqlCommand cmd = new SqlCommand(strCmd, conn))
            {
                cmd.Parameters.AddWithValue("@p1", _p1);
                cmd.Parameters.AddWithValue("@p2", _p2);

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
    }

    private void SaveCP3Record()
    {
        using (SqlConnection conn = new SqlConnection(strConn))
        {
            string strCmd = "IF EXISTS (SELECT * FROM compare_3_products " +
                                         "WHERE product_1 = @p1 and product_2 = @p2 and product_3 = @p3 ) " +
                                         "UPDATE compare_3_products SET [count]=[count]+1 " +
                                         "WHERE product_1 = @p1 and product_2 = @p2 and product_3 = @p3 " +
                                         "ELSE " +
                                         "INSERT INTO compare_3_products (product_1,product_2,product_3,[count]) VALUES (@p1,@p2,@p3,1)";
            using (SqlCommand cmd = new SqlCommand(strCmd, conn))
            {
                cmd.Parameters.AddWithValue("@p1", _p1);
                cmd.Parameters.AddWithValue("@p2", _p2);
                cmd.Parameters.AddWithValue("@p3", _p3);

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
    }

    private void SaveCP4Record()
    {
        using (SqlConnection conn = new SqlConnection(strConn))
        {
            string strCmd = "IF EXISTS (SELECT * FROM compare_4_products " +
                                         "WHERE product_1 = @p1 and product_2 = @p2 and product_3 = @p3 and product_4 = @p4 ) " +
                                         "UPDATE compare_4_products SET [count]=[count]+1 " +
                                         "WHERE product_1 = @p1 and product_2 = @p2 and product_3 = @p3 and product_4 = @p4 " +
                                         "ELSE " +
                                         "INSERT INTO compare_4_products (product_1,product_2,product_3,product_4,[count]) VALUES (@p1,@p2,@p3,@p4,1)";
            using (SqlCommand cmd = new SqlCommand(strCmd, conn))
            {
                cmd.Parameters.AddWithValue("@p1", _p1);
                cmd.Parameters.AddWithValue("@p2", _p2);
                cmd.Parameters.AddWithValue("@p3", _p3);
                cmd.Parameters.AddWithValue("@p4", _p4);

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
    }

    private void SaveCP5Record()
    {
        using (SqlConnection conn = new SqlConnection(strConn))
        {
            string strCmd = "IF EXISTS (SELECT * FROM compare_5_products "+
                                         "WHERE product_1 = @p1 and product_2 = @p2 and product_3 = @p3 and product_4 = @p4 and product_5 = @p5 ) " +
                                         "UPDATE compare_5_products SET [count]=[count]+1 "+
                                         "WHERE product_1 = @p1 and product_2 = @p2 and product_3 = @p3 and product_4 = @p4 and product_5 = @p5 " +
                                         "ELSE "+
                                         "INSERT INTO compare_5_products (product_1,product_2,product_3,product_4,product_5,[count]) VALUES (@p1,@p2,@p3,@p4,@p5,1)";
            using (SqlCommand cmd = new SqlCommand(strCmd, conn))
            {
                cmd.Parameters.AddWithValue("@p1", _p1);
                cmd.Parameters.AddWithValue("@p2", _p2);
                cmd.Parameters.AddWithValue("@p3", _p3);
                cmd.Parameters.AddWithValue("@p4", _p4);
                cmd.Parameters.AddWithValue("@p5", _p5);

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
    }



    public Product GetProductInfo(int ProductID)
    {
        UboxContext db = new UboxContext();
        var qp = (from p in db.ProductInfo
                          where ProductID == p.id
                          select new Product
                          {
                              ID = p.id,
                              Image = p.image,
                              Name = p.name,
                              Size = p.size,
                              Weight = p.weight ?? 0,
                              BatteryTalkTime = p.battery_talk_time ?? 0,
                              BatteryCapacity = p.battery_capacity,
                              Color = p.color,
                              Price = p.price ?? 0,
                              CategoryID = p.category_id,
                              Brand = p.brand,
                              Connect = p.connection_standard,
                              OperatingSystem = p.operating_system,
                              ProcessorCore = p.processor_core,
                              ProcessorSpeed = p.processor_speed,
                              ScreenSize = p.screen_size,
                              ScreenResolution = p.screen_resolution,
                              CameraResolutionFront = p.camera_resolution_front,
                              CameraResolutionRear = p.camera_resolution_rear,
                              Ram = p.ram,
                              Rom = p.rom
                          }).FirstOrDefault();
        return qp;
    }

    public List<Product> GetProductsInfo(int[] ProductsID)
    {
        List<Product> ps = new List<Product>();
        for (int i = 0; i < ProductsID.Length; i++)
        {
            ps.Add(GetProductInfo(ProductsID[i]));
        }
        return ps;
    }

    public ProductPreview GetProductPreview(int ProductID)
    {
        UboxContext db = new UboxContext();
        
        var pr = (from p in db.production_product
                          join i in db.production_image on p.product_id equals i.product_id into pi
                          from i in pi.DefaultIfEmpty()
                          where (p.product_id == ProductID) &&
                                       (i.image_order == 1)
                          select new ProductPreview
                          {
                              Name = p.product_name,
                              ID = p.product_id,
                              Image = i.image,
                              CategoryID = p.category_id
                          }).FirstOrDefault();

        return pr;
    }

    public List<ProductPreview> GetProductsPreview(int[] ProductsID)
    {
        List<ProductPreview> pros = new List<ProductPreview>();
        for (int i = 0; i < ProductsID.Length; i++)
        {
            pros.Add(GetProductPreview(ProductsID[i]));
        }
        return pros;
    }

    private ArrayList DistinctArray(ArrayList Array)
    {
        ArrayList DistinctArray = new ArrayList();
        foreach (int i in Array)
        {
            if (!DistinctArray.Contains(i))
            {
                DistinctArray.Add(i);
            }
        }
        return (DistinctArray);
    }

    public List<ProductPreview> GetProductsRefer(int[] ProductsID)
    {
        ArrayList prosId = new ArrayList();
        int count = ProductsID.Length;
        int p1, p2, p3, p4, p5;
        p1 = p2 = p3 = p4 = p5 = 0;

        for (int i = 0; i < count; i++)
        {
            if (i == 0)
            p1 = ProductsID[0];
            if (i == 1)
            p2 = ProductsID[1];
            if (i == 2)
            p3 = ProductsID[2];
            if (i == 3)
            p4 = ProductsID[3];
            if (i == 4)
            p5 = ProductsID[4];
        }

        #region SqlConnection
        using (SqlConnection conn = new SqlConnection(strConn))
        {
            string strCmd;
            switch (count)
            {                
                case 4:
                    strCmd = @"select * from
                                        (select * from
                                        (select * from
                                        (select product_1,product_2,product_3,product_4,product_5,[count] from compare_5_products
                                        where product_1 =@p1 or product_2 =@p1 or product_3 =@p1 or product_4 =@p1 or product_5 =@p1) as temp
                                        where product_1 =@p2 or product_2 =@p2 or product_3 =@p2 or product_4 =@p2 or product_5 =@p2) as temp
                                        where product_1 =@p3 or product_2 =@p3 or product_3 =@p3 or product_4 =@p3 or product_5 =@p3) as temp
                                        where product_1 =@p4 or product_2 =@p4 or product_3 =@p4 or product_4 =@p4 or product_5 =@p4 order by [count] desc";
                    break;                
                case 3:
                    strCmd = @"select * from
                                        (select * from
                                        (select product_1,product_2,product_3,product_4,[count] from compare_4_products
                                        where product_1 =@p1 or product_2 =@p1 or product_3 =@p1 or product_4 =@p1) as temp
                                        where product_1 =@p2 or product_2 =@p2 or product_3 =@p2 or product_4 =@p2) as temp
                                        where product_1 =@p3 or product_2 =@p3 or product_3 =@p3 or product_4 =@p3 order by [count] desc";
                    break;
                case 2:
                    strCmd = @"select * from
                                        (select product_1,product_2,product_3,[count] from compare_3_products
                                        where product_1 =@p1 or product_2 =@p1 or product_3 =@p1) as temp
                                        where product_1 =@p2 or product_2 =@p2 or product_3 =@p2 order by [count] desc";
                    break;
                default :
                    strCmd = @"select product_1, product_2,[count] from compare_2_products 
                                         where product_1 =@p1 or product_2 =@p1 order by [count] desc";
                    break;
            }
            #region SqlCommand
            using (SqlCommand cmd = new SqlCommand(strCmd, conn))
            {
                switch (count)
                {
                    case 4:
                        cmd.Parameters.AddWithValue("p1", p1);
                        cmd.Parameters.AddWithValue("p2", p2);
                        cmd.Parameters.AddWithValue("p3", p3);
                        cmd.Parameters.AddWithValue("p4", p4);
                        break;
                    case 3:
                        cmd.Parameters.AddWithValue("p1", p1);
                        cmd.Parameters.AddWithValue("p2", p2);
                        cmd.Parameters.AddWithValue("p3", p3);
                        break;
                    case 2:
                        cmd.Parameters.AddWithValue("p1", p1);
                        cmd.Parameters.AddWithValue("p2", p2);
                        break;
                    default:
                        cmd.Parameters.AddWithValue("p1", p1);
                        break;
                }
                conn.Open();
                
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    switch (count)
                    {
                        case 4:
                            while (reader.Read())
                            {
                                prosId.Add(reader[0]);
                                prosId.Add(reader[1]);
                                prosId.Add(reader[2]);
                                prosId.Add(reader[3]);
                                prosId.Add(reader[4]); 
                            }
                            prosId = DistinctArray(prosId);
                            prosId.Remove(p1);
                            prosId.Remove(p2);
                            prosId.Remove(p3);
                            prosId.Remove(p4);
                            break;
                        case 3:
                            while (reader.Read())
                            {
                                prosId.Add(reader[0]);
                                prosId.Add(reader[1]);
                                prosId.Add(reader[2]);
                                prosId.Add(reader[3]); 
                            }
                            prosId = DistinctArray(prosId);
                            prosId.Remove(p1);
                            prosId.Remove(p2);
                            prosId.Remove(p3);
                            break;
                        case 2:
                            while (reader.Read())
                            {
                                prosId.Add(reader[0]);
                                prosId.Add(reader[1]);
                                prosId.Add(reader[2]);
                            }
                            prosId = DistinctArray(prosId);
                            prosId.Remove(p1);
                            prosId.Remove(p2);
                            break;
                        default:
                            while (reader.Read())
                            {
                                prosId.Add(reader[0]);
                                prosId.Add(reader[1]);
                            }
                            prosId = DistinctArray(prosId);
                            prosId.Remove(p1);
                            break;
                    }
                 
                }
                conn.Close();

            }
            #endregion
        }
        #endregion
        for (int i = 0; i < prosId.Count; i++)
        {
            if (i >= 6)
            {
                prosId.RemoveAt(i);
                i--;
            }
        }
        List<ProductPreview> pr = GetProductsPreview((int[])prosId.ToArray(typeof(int)));
        return pr;

    }
    
}